\chapter{DBIx::Class and SQL::Abstract FAQ}
\label{chap:sqlabstract}

ORMs are nice, but they hide SQL from us.  This sometimes makes it
difficult to perform database work, which would be easier with direct
SQL.  So, this appendix presents several common situations, which
require special features of DBIx::Class and/or SQL::Abstract.
This presentation is in FAQ format.  Each section has a question
or problem and at least one solution to it.

\section{How do I put the current time into a database field?}
\label{sec:faqdbnow}

This depends on your database.  There are three databases I know about:
PostgreSQL, SQLite, and MySQL.  In PostgreSQL this is simple:

{\footnotesize
\begin{verbatim}
    $row->update( { modified => 'now' } );
\end{verbatim}
}

\noindent
The other two are harder and subtly different.  Here's SQLite:

{\footnotesize
\begin{verbatim}
    $row->update( { modified => \'datetime("now")' } );
\end{verbatim}
}

\noindent
MySQL is similar:

{\footnotesize
\begin{verbatim}
    $row->update( { modified => \'NOW()' } );
\end{verbatim}
}

\noindent
Note that in the last two cases there are quotes which look unmatched.  The
backslash is taking a reference to the scalar, not escaping the quote.

\section{How do I get a has-a\index{has-a}\index{belongs to} relationship?}

With bigtop, it is easy to get a has-a relationship:

{\footnotesize
\begin{verbatim}
    field other_table {
        is int4;
        refers_to other_table;
        html_form_type_select;
    }
\end{verbatim}
}

\noindent
The \verb+refers_to+ statement is the key.  It tells bigtop to create
a foreign key relationship.  In DBIx::Class, this produces:

{\footnotesize
\begin{verbatim}
__PACKAGE__->belongs_to(
        other_table => 'Your::Model::other_table'
);
\end{verbatim}
}

\noindent
If you hand code one of these, remember to put it in the stub rather than
in the GEN model module.

To use this in a controller, do something like this:

{\footnotesize
\begin{verbatim}
my $row       = $MY_TABLE->gfind( $self, $id );
my $foreigner = $row->other_table;
\end{verbatim}
}

\noindent
The object in \verb+foreigner+ is the row your foreign key points to.

\section{How do I get a has-many\index{has-many} relationship?}

The other side of a has-a relationship is the has-many relationship.
If a foreign table points to you, there could be many rows in it which
do.  If you want to access those directly you need a has-many relationship.
Put something like this in your model's stub module:

{\footnotesize
\begin{verbatim}
    __PACKAGE__->has_many(
        friends => 'Your::Model::friend', 'other_table'
    );
\end{verbatim}
}

\noindent
Use these in a controller like so:

{\footnotesize
\begin{verbatim}
my $row     = $OTHER_TABLE->gfind( $self, $id );
my @friends = $row->friends;
\end{verbatim}
}

\section{How do I get a many-to-many\index{many-to-many} relationship?}

There is an example of this situation in Section \ref{sec:many2many}, but
here is a summary.

Suppose you have a relationship between book and author.
Each book has one or more authors; each author has one
or more books.  This requires a three table structure with a book
table, an author table, and a joining table with foreign keys to both
book and author.

In addition to creating the tables in the database, you need to note
the relationships between the tables in their DBIx::Class models.
The easiest way to get that extra code is to put this in your bigtop file:

{\footnotesize
\begin{verbatim}
    join_table book_author {
        joins book => author;
    }
\end{verbatim}
}

\noindent
Alternatively, you could add code by hand code.  Start in the \verb+book+
table:

{\footnotesize
\begin{verbatim}
    __PACKAGE__->has_many(
        book_authors => 'Your::Model::book_author', 'book'
    );
    __PACKAGE__->many_to_many(
        authors => 'book_authors', 'author'
    );
\end{verbatim}
}

\noindent
Do the same for \verb+author+, but reverse the names:

{\footnotesize
\begin{verbatim}
    __PACKAGE__->has_many(
        book_authors => 'Your::Model::book_author', 'author'
    );
    __PACKAGE__->many_to_many(
        books => 'book_authors', 'book'
    );
\end{verbatim}
}

\noindent
Finally, do the straightforward thing for \verb+book_author+:

{\footnotesize
\begin{verbatim}
    __PACKAGE__->belongs_to( book => 'Your::Model::book' );
    __PACKAGE__->belongs_to( author => 'Your::Model::author' );
\end{verbatim}
}

\section{How do I use a many-to-many\index{many-to-many} in the simplest way?}

Suppose you've set up a many-to-many from the previous question,
to get all books by a certain author, add this to your Gantry controller:

{\footnotesize
\begin{verbatim}
    my $author = $AUTHOR->gfind( $site, $author_id );
    my @books  = $author->books;
\end{verbatim}
}

\noindent
If you need to do the same thing in a script, use \verb+sfind+ instead:

{\footnotesize
\begin{verbatim}
    my $author = $AUTHOR->sfind( $schema, $author_id );
    my @books  = $author->books;
\end{verbatim}
}

\subsection*{What if I needed to limit the search or order the results?}

Start with the other table and join through the three way table:

{\footnotesize
\begin{verbatim}
    my @books = $BOOK->gsearch(
            $site,
            {
                'book_authors.author' => $author_id,
            },
            {
                join     => [ 'book_authors' ],
                order_by => 'last_name, first_name',
            },
    );
\end{verbatim}
}

\section{What if I need a left outer\index{left outer join}\index{outer
join, left} join\index{join, left outer}?}

See the next question for the example names here (or if you don't know
when you need left outer joins).

Create a special \verb+has_many+ in your model stub:

{\footnotesize
\begin{verbatim}
    __PACKAGE__->has_many(
        outer_co_owners => 'Your::Model::list_co_owners',
        'co_owner',
        { join_type => 'LEFT OUTER' }
    );
\end{verbatim}
}

\noindent
Then use the has many as needed:

{\footnotesize
\begin{verbatim}
    my @owned = $LIST->gsearch(
            $site,
            {
                -or => {
                    'me.owner'                 => $owner_id,
                    'outer_co_owners.co_owner' => $owner_id,
                },
            },
            {
                join     => [ 'outer_co_owners' ],
                order_by => 'title',
            }
    );
\end{verbatim}
}

\section{Why would I ever need a left outer join?}

Suppose you allow owners of some resource, like a mailing list.  Further,
suppose there could be co-owners -- say to take over when the actual
owner is on vacation.  But, not every mailing list has co-owners.
This leads to two tables: \verb+list+ and
\verb+list_co_owner+.  Here are the important fields in \verb+list+:\\

\begin{tabular}{l|l}
Name  & Use \\
\hline
\verb+id+    & primary key       \\
\verb+title+ & mailing list name \\
\verb+owner+ & user id of owner  \\
\end{tabular}

\vspace{.1in}

And, here they are for \verb+list_co_owner+:\\

\begin{tabular}{l|l}
Name  & Use \\
\hline
\verb+id+       & primary key         \\
\verb+list+     & list foreign key    \\
\verb+co_owner+ & user id of co-owner \\
\end{tabular}

\vspace{.1in}
A regular join on \verb+list.id+ equaling \verb+list_co_owner.list+
will result in extraneous rows (lots of them) for any list which does not
have co-owners.  Thus, you need a LEFT OUTER join to exclude those
lists which have no co-owners.  In SQL that looks like this:

{\footnotesize
\begin{verbatim}
SELECT title, owner, co_owner
FROM list LEFT OUTER JOIN list_co_owner
ON ( list.id = list_co_owner.list )
WHERE owner = 12 or co_owner = 12;
\end{verbatim}
}
